﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace LINQ_test_console
{
    [Table(Name = "Studenti")]
    public class Student
    {
        
        private string _Indeks;

        public Student()
        {
           this._Ispiti = new EntitySet<Ispit>();
        }

        private EntitySet<Ispit> _Ispiti;
        [Association(Storage = "_Ispiti", OtherKey = "StudentIndeks")]
        public EntitySet<Ispit> Ispiti
        {
            get { return this._Ispiti; }
            set { this._Ispiti.Assign(value); }
        }

        [Column(IsPrimaryKey=true, Storage="_Indeks")]
        public string Indeks
        {
            get
            {
                return this._Indeks;
            }
            set
            {
                this._Indeks = value;
            }

        }

        private string _Grad;
        [Column(Storage="_Grad")]
        public string Grad
        {
            get
            {
                return this._Grad;
            }
            set
            {
                this._Grad=value;
            }
        }

        private string _Ime;
        [Column(Storage="_Ime")]
        public string Ime
        {
            get
            {
                return this._Ime;
            }
            set
            {
                this._Ime=value;
            }
        }

    }

    [Table(Name = "Predmeti")]
    public class Predmet
    {
        private int _ID;
        [Column(IsPrimaryKey = true, Storage = "_ID")]
        public int ID
        {
            get
            {
                return this._ID;
            }
            set
            {
                this._ID = value;
            }

        }

        private EntitySet<Ispit> _Ispiti;
        [Association(Storage = "_Ispiti", OtherKey = "PredmetID")]
        public EntitySet<Ispit> Ispiti
        {
            get { return this._Ispiti; }
            set { this._Ispiti.Assign(value); }
        }

        private int _Semestar;
        [Column(Storage = "_Semestar")]
        public int Semestar
        {
            get
            {
                return this._Semestar;
            }
            set
            {
                this._Semestar = value;
            }

        }

        private string _Naziv;
        [Column(Storage = "_Naziv")]
        public string Naziv
        {
            get
            {
                return this._Naziv;
            }
            set
            {
                this._Naziv = value;
            }

        }

        private string _Profesor;
        [Column(Storage = "_Profesor")]
        public string Profesor
        {
            get
            {
                return this._Profesor;
            }
            set
            {
                this._Profesor = value;
            }

        }
    }

    [Table(Name = "Ispiti")]
    public class Ispit
    {        
        private EntityRef<Student> _Student;
        private EntityRef<Predmet> _Predmet;
        public Ispit() { 
            this._Student = new EntityRef<Student>(); this._Predmet = new EntityRef<Predmet>(); 
        }


        private int _ID;
        [Column(IsPrimaryKey = true, Storage = "_ID", IsDbGenerated = true, CanBeNull = false, DbType = "int IDENTITY NOT NULL", UpdateCheck = UpdateCheck.Never)]
        public int ID
        {
            get
            {
                return this._ID;
            }
            set
            {
                this._ID = value;
            }

        }

        private DateTime _Datum;
        [Column(Storage = "_Datum")]
        public DateTime Datum
        {
            get
            {
                return this._Datum;
            }
            set
            {
                this._Datum = value;
            }
        }

        private int _Ocenka;
        [Column(Storage = "_Ocenka")]
        public int Ocenka
        {
            get
            {
                return this._Ocenka;
            }
            set
            {
                this._Ocenka = value;
            }

        }

        private string _StudentIndeks;
        [Column(Storage = "_StudentIndeks")]
        public string StudentIndeks
        {
            get { return this._StudentIndeks; }
            set { this._StudentIndeks = value; }
        }

        private int _PredmetID;
        [Column(Storage = "_PredmetID")]
        public int PredmetID
        {
            get { return this._PredmetID; }
            set { this._PredmetID = value; }
        }

        [Association(Storage = "_Student", ThisKey = "StudentIndeks")]
        public Student Student
        {
            get { return this._Student.Entity; }
            set { this._Student.Entity = value; }
        }
        [Association(Storage = "_Predmet", ThisKey = "PredmetID")]
        public Predmet Predmet
        {
            get { return this._Predmet.Entity; }
            set { this._Predmet.Entity = value; }
        }
    }


    class Program
    {
        static void Main(string[] args)
        {
            // Konekciski string.
            DataContext db = new DataContext(@"Database1.sdf");
            // Zemi tabela.
            Table<Student> Studenti = db.GetTable<Student>();
            Table<Predmet> Predmeti = db.GetTable<Predmet>();
            Table<Ispit> Ispiti = db.GetTable<Ispit>();

            // Zakaci mu log za da go prikaze SQL-ot so go generira.
            // db.Log = Console.Out;

            // Query za Studenti od Strumica.
            IQueryable<Student> stQuery =
                from st in Studenti
                where st.Grad == "Strumica"
                select st;

            Console.WriteLine("Site studenti od Strumica:\n");
            foreach (Student st in stQuery)
            {
                Console.WriteLine("Indeks={0}, Ime={1}, Grad={2}", st.Indeks,
                    st.Ime, st.Grad);
            }

            // Query za Predmeti koi gi predava prof. Dejan Gjorgjevikj
            Console.WriteLine("\n\nPredmeti koi gi predava prof. Dejan Gjorgjevikj:\n");
            IQueryable<Predmet> prQuery =
               from pr in Predmeti
               where pr.Profesor == "Dejan Gjorgjevikj"
               select pr;

            foreach (Predmet pr in prQuery)
            {
                Console.WriteLine("Semestar={0}, Naziv={1}", pr.Semestar,
                    pr.Naziv);
            }


            // Query za site polozeni ispiti na studenti cie ime zapocnuva so "Ma"
            Console.WriteLine("\n\nSite polozeni ispiti na studenti cie ime zapocnuva so \"Ma\":\n");
            IQueryable<Ispit> IspitQuery =
                from ispit in Ispiti
                where (ispit.Ocenka > 5) && (ispit.Student.Ime.StartsWith("Ma"))
                select ispit;

            foreach (Ispit isp in IspitQuery)
            {
                Console.WriteLine("{0} ({1}) - {2}: {3}", isp.Student.Ime, isp.Student.Indeks, isp.Predmet.Naziv,
               isp.Ocenka);
            }


            // Query za site polozeni ispiti na student cie ime e "Chuck Norris"
            Console.WriteLine("\n\nSite polozeni ispiti na student cie ime e \"Chuck Norris\":\n");
            IQueryable<Ispit> IspitQuery2 =
                from ispit in Ispiti
                where (ispit.Ocenka > 5) && (ispit.Student.Ime==("Chuck Norris"))
                select ispit;

            foreach (Ispit isp in IspitQuery2)
            {
                Console.WriteLine("{0} ({1}) - {2}: {3}", isp.Student.Ime, isp.Student.Indeks, isp.Predmet.Naziv,
               isp.Ocenka);
            }
      

            //nov ispit

            Ispit nov = new Ispit();
            
            IQueryable<Predmet> prQuery2 =
                from pr in Predmeti
                where (pr.Naziv == "Mrezno programiranje")
                select pr;

            nov.Predmet = prQuery2.First();
            nov.Datum = DateTime.Now;
            nov.StudentIndeks = "000/2013";
            nov.Ocenka = 20;
            nov.PredmetID = nov.Predmet.ID;
            nov.ID = 80;
            Ispiti.InsertOnSubmit(nov);
           
            db.SubmitChanges();
            
           

            // Query za site polozeni ispiti na student cie ime e "Chuck Norris" posle dodavanje na nov ispit
            Console.WriteLine("\n\nSite polozeni ispiti na student cie ime e \"Chuck Norris\" posle dodavanje na nov ispit:\n");
            IQueryable<Ispit> IspitQuery3 =
                from ispit in Ispiti
                where (ispit.Ocenka > 5) && (ispit.Student.Ime == ("Chuck Norris"))
                select ispit;

            foreach (Ispit isp in IspitQuery3)
            {
                Console.WriteLine("{0} ({1}) - {2}: {3}", isp.Student.Ime, isp.Student.Indeks, isp.Predmet.Naziv,
               isp.Ocenka);
            }

            //brisenje na dodadeniot ispit
            Ispiti.DeleteOnSubmit(nov);
            db.SubmitChanges();


            Console.ReadLine();
        }
    }
}
